/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package models.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import models.DBHandler;
import models.beans.MessageBroads;
import models.beans.MessageBroadsBean;
import models.beans.UserBean;

/**
 *
 * @author Group 3 Aprotrain Aptech Group3 Aprotrain Aptech
 */
public class MessageBroadDAO {
    public static List<MessageBroadsBean> getAllUsersByCondition() throws ClassNotFoundException, SQLException {
        List<MessageBroadsBean> list = new ArrayList<MessageBroadsBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblMessageBroads");
        rs = pstm.executeQuery();

        MessageBroadsBean messageBroads = null;
        while (rs.next()) {
            messageBroads = new MessageBroadsBean();
            messageBroads.setMessageId(rs.getInt("messageId"));
            String valueTitle = "";
            String valueContent = "";
            String title = rs.getString("title");
            if (title == null || title.length() <= 0) {
            valueTitle = "_";
            } else if (title.length() <= 30) {
                valueTitle = title;
            } else {
                valueTitle = title.substring(0, 30);
            }

            messageBroads.setTitle(valueTitle);
            String content = rs.getString("content");
            if (content == null || content.length() <= 0) {
            valueContent = "_";
            } else if (content.length() <= 30) {
                valueContent = content;
            } else {
                valueContent = content.substring(0, 30);
            }
            messageBroads.setContent(valueContent);
            messageBroads.setDate(convertDateToString(rs.getDate("date")));
            messageBroads.setAuthor(rs.getString("author"));
            messageBroads.setAttachment(rs.getString("attachment"));
            messageBroads.setDeadline(convertDateToString(rs.getDate("deadline")));
            list.add(messageBroads);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static List<MessageBroadsBean> getMessageBroads() throws ClassNotFoundException, SQLException {
        List<MessageBroadsBean> list = new ArrayList<MessageBroadsBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblMessageBroads");
        rs = pstm.executeQuery();

        MessageBroadsBean messageBroads = null;
        while (rs.next()) {
            messageBroads = new MessageBroadsBean();
            messageBroads.setMessageId(rs.getInt("messageId"));
            messageBroads.setTitle(rs.getString("title"));
            String ct = rs.getString("content");
            messageBroads.setContent(ct);
            messageBroads.setDate(convertDateToString(rs.getDate("date")));
            messageBroads.setAuthor(rs.getString("author"));
            messageBroads.setAttachment(rs.getString("attachment"));
            messageBroads.setDeadline(convertDateToString(rs.getDate("deadline")));
            list.add(messageBroads);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static List<MessageBroadsBean> getMessageBroadsUpcoming() throws ClassNotFoundException, SQLException {
        List<MessageBroadsBean> list = new ArrayList<MessageBroadsBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT TOP 3 * FROM tblMessageBroads ORDER BY date asc");
        rs = pstm.executeQuery();

        MessageBroadsBean messageBroads = null;
        while (rs.next()) {
            messageBroads = new MessageBroadsBean();
            messageBroads.setMessageId(rs.getInt("messageId"));
            messageBroads.setTitle(rs.getString("title"));
            String ct = rs.getString("content");
            messageBroads.setContent(ct);
            messageBroads.setDate(convertDateToString(rs.getDate("date")));
            messageBroads.setAuthor(rs.getString("author"));
            messageBroads.setAttachment(rs.getString("attachment"));
            messageBroads.setDeadline(convertDateToString(rs.getDate("deadline")));
            list.add(messageBroads);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static MessageBroadsBean getMessageBroadsById(int messId) throws ClassNotFoundException, SQLException {        
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblMessageBroads WHERE messageId=?");
        pstm.setInt(1, messId);
        rs = pstm.executeQuery();

        MessageBroadsBean messageBroads = null;
        if (rs.next()) {
            messageBroads = new MessageBroadsBean();
            messageBroads.setMessageId(rs.getInt("messageId"));
            messageBroads.setTitle(rs.getString("title"));
            messageBroads.setContent(rs.getString("content"));
            messageBroads.setDate(convertDateToString(rs.getDate("date")));
            messageBroads.setAuthor(rs.getString("author"));
            messageBroads.setAttachment(rs.getString("attachment"));
            messageBroads.setDeadline(convertDateToString(rs.getDate("deadline")));
        }
        else{            
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return messageBroads;
    }
    public static int addNewMessageBroads(MessageBroadsBean mess) throws ClassNotFoundException, SQLException {
        int result = 0;        
        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("INSERT INTO [tblMessageBroads] ([title],[content],[date],[author],[attachment],[deadline])VALUES(?,?,?,?,?,?)");
        pstmt.setNString(1, mess.getTitle());
        pstmt.setNString(2, mess.getContent());
        pstmt.setDate(3, getInsertDate(mess.getDate()));
        pstmt.setNString(4, mess.getAuthor());
        pstmt.setNString(5, mess.getAttachment());
        pstmt.setDate(6, getInsertDate(mess.getDeadline()));
        result = pstmt.executeUpdate();
        
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    public static int updateMessageBroads(MessageBroadsBean mess) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("UPDATE [tblMessageBroads] SET [title]=?,[content]=?,[date]=?,[author]=?,[attachment]=?,[deadline]=? WHERE [messageId]=?");
        pstmt.setNString(1, mess.getTitle());
        pstmt.setNString(2, mess.getContent());
        pstmt.setDate(3, getInsertDate(mess.getDate()));
        pstmt.setNString(4, mess.getAuthor());
        pstmt.setNString(5, mess.getAttachment());
        pstmt.setDate(6, getInsertDate(mess.getDeadline()));
        pstmt.setInt(7, mess.getMessageId());
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    public static int deleteMessageBroads(int messageId) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection oconn = DBHandler.getConnection();
        PreparedStatement pstmt = oconn.prepareStatement("DELETE FROM [tblMessageBroads] WHERE messageId=?");
        pstmt.setInt(1, messageId);
        result = pstmt.executeUpdate();
        DBHandler.closeDBObj(pstmt);
        DBHandler.closeDBObj(oconn);
        return result;
    }
    public static Date getInsertDate(String s){
        java.sql.Date dte=null;
        try{
        String str = s;
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        java.util.Date dt = formatter.parse(str);
        dte=new java.sql.Date(dt.getTime());
        }catch(Exception e){
        e.printStackTrace();
        }
        return dte;
    }
    public static String convertDateToString(java.util.Date date) {
        SimpleDateFormat mySimpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
        return mySimpleDateFormat.format(date);
    }
}
